package com.example.ems.dao;

import com.example.ems.bean.Employee;
import com.example.ems.util.DBUtil;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class EmployeeDAO {
    public List<Employee> getAllEmployees() {
        List<Employee> employees = new ArrayList<>();
        String sql = "SELECT e.*, d.name as dept_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.id ORDER BY e.name";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            while (rs.next()) {
                Employee emp = new Employee();
                emp.setId(rs.getInt("id"));
                emp.setName(rs.getString("name"));
                emp.setEmail(rs.getString("email"));
                emp.setPosition(rs.getString("position"));
                emp.setDeptId(rs.getInt("dept_id"));
                emp.setDeptName(rs.getString("dept_name"));
                emp.setHireDate(rs.getDate("hire_date"));
                employees.add(emp);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return employees;
    }

    public Employee getEmployeeById(int id) {
        String sql = "SELECT e.*, d.name as dept_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.id WHERE e.id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, id);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    Employee emp = new Employee();
                    emp.setId(rs.getInt("id"));
                    emp.setName(rs.getString("name"));
                    emp.setEmail(rs.getString("email"));
                    emp.setPosition(rs.getString("position"));
                    emp.setDeptId(rs.getInt("dept_id"));
                    emp.setDeptName(rs.getString("dept_name"));
                    emp.setHireDate(rs.getDate("hire_date"));
                    return emp;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public boolean addEmployee(Employee employee) {
        String sql = "INSERT INTO employees (name, email, position, dept_id, hire_date) VALUES (?, ?, ?, ?, ?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, employee.getName());
            pstmt.setString(2, employee.getEmail());
            pstmt.setString(3, employee.getPosition());
            if (employee.getDeptId() == 0) { // Handle case where department might not be selected
                pstmt.setNull(4, Types.INTEGER);
            } else {
                pstmt.setInt(4, employee.getDeptId());
            }
            pstmt.setDate(5, employee.getHireDate() != null ? new java.sql.Date(employee.getHireDate().getTime()) : null);
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    public boolean updateEmployee(Employee employee) {
        String sql = "UPDATE employees SET name = ?, email = ?, position = ?, dept_id = ?, hire_date = ? WHERE id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, employee.getName());
            pstmt.setString(2, employee.getEmail());
            pstmt.setString(3, employee.getPosition());
            if (employee.getDeptId() == 0) {
                pstmt.setNull(4, Types.INTEGER);
            } else {
                pstmt.setInt(4, employee.getDeptId());
            }
            pstmt.setDate(5, employee.getHireDate() != null ? new java.sql.Date(employee.getHireDate().getTime()) : null);
            pstmt.setInt(6, employee.getId());
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    public boolean deleteEmployee(int id) {
        String sql = "DELETE FROM employees WHERE id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, id);
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    public List<Employee> getEmployeesByName(String searchName) {
        List<Employee> employees = new ArrayList<>();
        String sql = "SELECT e.*, d.name as dept_name FROM employees e " +
                "LEFT JOIN departments d ON e.dept_id = d.id " +
                "WHERE e.name LIKE ? " + // 按员工姓名模糊查询
                "ORDER BY e.name";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, "%" + searchName + "%");
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    Employee emp = new Employee();
                    emp.setId(rs.getInt("id"));
                    emp.setName(rs.getString("name"));
                    emp.setEmail(rs.getString("email"));
                    emp.setPosition(rs.getString("position"));
                    emp.setDeptId(rs.getInt("dept_id"));
                    emp.setDeptName(rs.getString("dept_name"));
                    emp.setHireDate(rs.getDate("hire_date"));
                    employees.add(emp);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return employees;
    }

}
